package DataLayer.DB;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;

/**
 * @Author Kis Boisen Hansen
 * @Version 2006.10.02 - revised marts 2009 FEN
 * @version 2010.10.07 - new connection
 * @version 2011.11.02 - new methods to handle transaction This class is used to create the
 *          connection to the database. It is implemented as a singleton. The constructor is private
 *          to encure that only one object of the class is generated Version for Sql Server or MySql
 *          lines in comments
 */
public class DBConnection
{ // constants used to get access to the database
	private static final String driver = "jdbc:sqlserver://balder.ucn.dk:1433";
	private static final String databaseName = ";databaseName=dmaa0912_4";
	private static String userName = "; user=dmaa0912_4";
	private static String password = ";password=MaaGodt";
	private DatabaseMetaData dma;
	private static Connection con;
	// an instance of the class is generetated
	private static DBConnection instance = null;

	// the constructor is private to ensure that only one object of this class
	// is created
	private DBConnection()
	{
		String url = driver + databaseName + userName + password;
		try
		{
			// load driver
			// SQL Server
			// Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
			Class.forName("org.hsqldb.jdbcDriver");
			System.out.println("Load af class ok");
		}
		catch (Exception e)
		{
			System.out.println("Can't find the driver");
			System.out.println(e.getMessage());
		}// end catch
		try
		{
			// Use remote db
			// con = DriverManager.getConnection(url);
			// Use local db
			con = DriverManager.getConnection("jdbc:hsqldb:file:9kpainting", "SA", "");
			con.setAutoCommit(true); // set autocommit
			dma = con.getMetaData(); // get metadata
			System.out.println("Connection to " + dma.getURL());
			System.out.println("Driver " + dma.getDriverName());
			System.out.println("Database product name " +
					dma.getDatabaseProductName());
		}// end try
		catch (Exception e)
		{
			System.out.println("Problems with the connection to the database");
			System.out.println(e.getMessage());
			System.out.println(url);
		}// end catch
	}// end constructor

	// closeDb: closes the connection to the database
	public static void closeConnection()
	{
		try
		{
			con.close();
			System.out.println("The connection is closed");
		}
		catch (Exception e)
		{
			System.out.println("Error trying to close the database "
					+ e.getMessage());
		}
	}// end closeDB

	// getDBcon is used to get the instance of the connection
	public Connection getDBcon()
	{
		return con;
	}

	public static DBConnection getInstance()
	{
		if (instance == null)
		{
			instance = new DBConnection();
		}
		return instance;
	}

	public static void startTransaction()
	{
		try
		{
			con.setAutoCommit(false);
		}
		catch (Exception e)
		{
			System.out.println("error - start transaction");
			System.out.println(e.getMessage());
		}
	}

	public static void commitTransaction()
	{
		try
		{
			con.setAutoCommit(true);
		}
		catch (Exception e)
		{
			System.out.println("error - commit transaction");
			System.out.println(e.getMessage());
		}
	}

	public static void rollbackTransaction()
	{
		try
		{
			con.rollback();
			con.setAutoCommit(true);
		}
		catch (Exception e)
		{
			System.out.println("error - rollback transaction");
			System.out.println(e.getMessage());
		}
	}
}// end DbConnection
